﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RL
{
    public class ReportRouteList
    {
        private routelistEntities db;

        public ReportRouteList()
        {
            db = new routelistEntities();
        }

        public IEnumerable<TechReportData> makeReportTechReport(DateTime beg_date, DateTime end_date)
        {
            IQueryable<RouteList> RL = from o in db.RouteList where o.dt_datedoc >= beg_date && o.dt_datedoc < end_date select o;
            IQueryable<VokzalTable> StationTable = from o in db.VokzalTable join a in RL on o.id_routelist equals a.id select o;
            IQueryable<RouteListInfo> Info = from o in db.RouteListInfo join a in RL on o.id_rl equals a.id select o;
            var grpInfo = from o in Info group o by o.id_route into grp select new {id_route=grp.Key, length = grp.Sum(a=>a.length_fact), free_length = grp.Sum(a=>a.free_length_fact), plan_reis=grp.Sum(a=>a.reis), fact_reis=grp.Sum(a=>a.reis_fact), work_time=grp.Sum(a=>a.WORK_TIME), rest_time=grp.Sum(a=>a.rest_time), plan=grp.Sum(a=>a.price), cash_fact=grp.Sum(a=>a.price_fact)};
            var grpStationTable = from o in StationTable group o by o.id_route into grp select new { id_route = grp.Key, qnt_tickets = grp.Sum(a => a.qnt_tickets), summa = grp.Sum(a => a.summa_tickets), summa_insurance = grp.Sum(a => a.sum_insurance), summa_bagazh = grp.Sum(a => a.bagazh) };
            var svod = from o in grpInfo join b in grpStationTable on o.id_route equals b.id_route into gj from subb in gj.DefaultIfEmpty() select new {id_route = o.id_route, length = o.length, free_length = o.free_length, fact_reis = o.fact_reis, plan_reis = o.plan_reis, plan = o.plan, cash = (o.cash_fact == null ? 0 : o.cash_fact), work_time = o.work_time, rest_time = o.rest_time, qnt_tickets = (subb.qnt_tickets==null?0:subb.qnt_tickets), summ_tickets=(subb.summa == null ? 0 : subb.summa), summ_insurance=(subb.summa_insurance == null ? 0 : subb.summa_insurance), summ_bagazh = (subb.summa_bagazh == null ? 0 : subb.summa_bagazh)};
            var fullinfo = from o in svod join b in db.Routes on o.id_route equals b.id into gj from z in gj.DefaultIfEmpty() select new { routeInfo = (z.number == null ? "<марш. не опред.>" : z.number.Trim() + " " + z.name.Trim()), averPriceRoute = (z.price == null ? 0 : z.price), routeType = (z.RouteType.name == null ? "<вид не опред.>" : z.RouteType.name), length = o.length, free_length = o.free_length, reis_fact = o.fact_reis, reis_plan = o.plan_reis, plan = o.plan, cash = o.cash, workTime = o.work_time, restTime = o.rest_time, people_ticket = o.qnt_tickets, station = o.summ_tickets, insurance = o.summ_insurance, bagazh = o.summ_bagazh};
            var resultinfo = from o in fullinfo orderby o.routeType,o.routeInfo select new TechReportData{routeInfo=o.routeInfo, routeType=o.routeType, averPriceRoute = o.averPriceRoute==null?0:o.averPriceRoute.Value, workTime=o.workTime==null?0:o.workTime.Value,restTime=o.restTime==null?0:o.restTime.Value,length=o.length==null?0:o.length.Value,free_length=o.free_length==null?0:o.free_length.Value,cash=o.cash==null?0:o.cash.Value,station=o.station, insurance=o.insurance, bagazh=o.bagazh, plan=o.plan==null?0:o.plan.Value,people_ticket=o.people_ticket,people_cash=(o.averPriceRoute!= null && o.averPriceRoute.Value!=0 && o.cash != null)?(o.cash.Value/o.averPriceRoute.Value):0, reis_plan=o.reis_plan==null?0:o.reis_plan.Value, reis_fact = o.reis_fact==null?0:o.reis_fact.Value};
            return resultinfo;
        }

        public IEnumerable<TechReportDataByDriver> makeReportTechReportByDriver(DateTime beg_date, DateTime end_date)
        {
            IQueryable<RouteList> RL = from o in db.RouteList where o.dt_datedoc >= beg_date && o.dt_datedoc < end_date select o;
            var StationTable = from o in db.VokzalTable join a in RL on o.id_routelist equals a.id select new { id_route = o.id_route, id_routetype = o.Routes.id_routetype, id_driver = a.id_driver, qnt_tickets = o.qnt_tickets, summa_tickets = o.summa_tickets, sum_insurance = o.sum_insurance, bagazh = o.bagazh };
            var Info = from o in db.RouteListInfo join a in RL on o.id_rl equals a.id select new {id_route = o.id_route, price = o.price, length = o.length, free_length = o.free_length, reis = o.reis, WORK_TIME = o.WORK_TIME, rest_time = o.rest_time, id_rl = o.id_rl, reis_fact = o.reis_fact, length_fact = o.length_fact, free_length_fact = o.free_length_fact, price_fact = o.price_fact, id_driver=a.id_driver, id_routetype = o.Routes.id_routetype };
            var grpInfo = from o in Info group o by new { o.id_driver, o.id_routetype } into grp select new { id_driver = grp.Key.id_driver, id_routetype = grp.Key.id_routetype, length = grp.Sum(a => a.length_fact), free_length = grp.Sum(a => a.free_length_fact), plan_reis = grp.Sum(a => a.reis), fact_reis = grp.Sum(a => a.reis_fact), work_time = grp.Sum(a => a.WORK_TIME), rest_time = grp.Sum(a => a.rest_time), plan = grp.Sum(a => a.price), cash_fact = grp.Sum(a => a.price_fact) };
            var grpStationTable = from o in StationTable group o by new { o.id_driver, o.id_routetype } into grp select new { id_driver=grp.Key.id_driver, id_routetype = grp.Key.id_routetype, qnt_tickets = grp.Sum(a => a.qnt_tickets), summa = grp.Sum(a => a.summa_tickets), summa_insurance = grp.Sum(a => a.sum_insurance), summa_bagazh = grp.Sum(a => a.bagazh) };
            var svod = from o in grpInfo join b in grpStationTable on new { o.id_routetype, o.id_driver } equals new { b.id_routetype, b.id_driver } into gj from subb in gj.DefaultIfEmpty() select new { id_driver=o.id_driver, id_routetype = o.id_routetype, length = o.length, free_length = o.free_length, fact_reis = o.fact_reis, plan_reis = o.plan_reis, plan = o.plan, cash = (o.cash_fact == null ? 0 : o.cash_fact), work_time = o.work_time, rest_time = o.rest_time, qnt_tickets = (subb.qnt_tickets == null ? 0 : subb.qnt_tickets), summ_tickets = (subb.summa == null ? 0 : subb.summa), summ_insurance = (subb.summa_insurance == null ? 0 : subb.summa_insurance), summ_bagazh = (subb.summa_bagazh == null ? 0 : subb.summa_bagazh) };
            var fullinfo1 = from o in svod join b in db.RouteType on o.id_routetype equals  b.id  into gj from z in gj.DefaultIfEmpty() select new {id_driver = o.id_driver, routeType = (z.name == null ? "<вид. марш. не опред.>" : z.name.Trim()), length = o.length, free_length = o.free_length, reis_fact = o.fact_reis, reis_plan = o.plan_reis, plan = o.plan, cash = o.cash, workTime = o.work_time, restTime = o.rest_time, people_ticket = o.qnt_tickets, station = o.summ_tickets, insurance = o.summ_insurance, bagazh = o.summ_bagazh };
            var fullinfo = from o in fullinfo1 join b in db.driver on o.id_driver equals b.id into gj from z in gj.DefaultIfEmpty() select new { DriverTabel = (z.person.tabel == null ? "<>" : z.person.tabel.Trim()), DriverFio = (z.person.fio == null ? "<сотр. не опред.>" : z.person.fio.Trim()), routeType = o.routeType, length = o.length, free_length = o.free_length, reis_fact = o.reis_fact, reis_plan = o.reis_plan, plan = o.plan, cash = o.cash, workTime = o.workTime, restTime = o.restTime, people_ticket = o.people_ticket, station = o.station, insurance = o.insurance, bagazh = o.bagazh };
            var resultinfo = from o in fullinfo orderby o.DriverFio, o.routeType select new TechReportDataByDriver {driverTabel = o.DriverTabel, driverFIO = o.DriverFio,routeType = o.routeType, workTime = o.workTime == null ? 0 : o.workTime.Value, restTime = o.restTime == null ? 0 : o.restTime.Value, length = o.length == null ? 0 : o.length.Value, free_length = o.free_length == null ? 0 : o.free_length.Value, cash = o.cash == null ? 0 : o.cash.Value, station = o.station, insurance = o.insurance, bagazh = o.bagazh, plan = o.plan == null ? 0 : o.plan.Value, people_ticket = o.people_ticket, reis_plan = o.reis_plan == null ? 0 : o.reis_plan.Value, reis_fact = o.reis_fact == null ? 0 : o.reis_fact.Value };
            return resultinfo;
        }

        public IEnumerable<TechReportDataByVehicleModel> makeReportTechReportByVehicleModel(DateTime beg_date, DateTime end_date)
        {
            IQueryable<RouteList> RL = from o in db.RouteList where o.dt_datedoc >= beg_date && o.dt_datedoc < end_date select o;
            var StationTable = from o in db.VokzalTable join a in RL on o.id_routelist equals a.id select new { id_route = o.id_route, id_routetype = o.Routes.id_routetype, id_vehicleType = a.vehicle.id_type, qnt_tickets = o.qnt_tickets, summa_tickets = o.summa_tickets, sum_insurance = o.sum_insurance, bagazh = o.bagazh };
            var Info = from o in db.RouteListInfo join a in RL on o.id_rl equals a.id select new { id_route = o.id_route, price = o.price, length = o.length, free_length = o.free_length, reis = o.reis, WORK_TIME = o.WORK_TIME, rest_time = o.rest_time, id_rl = o.id_rl, reis_fact = o.reis_fact, length_fact = o.length_fact, free_length_fact = o.free_length_fact, price_fact = o.price_fact, id_vehicleType = a.vehicle.id_type, id_routetype = o.Routes.id_routetype };
            var grpInfo = from o in Info group o by new { o.id_vehicleType, o.id_routetype } into grp select new { id_vehicleType = grp.Key.id_vehicleType, id_routetype = grp.Key.id_routetype, length = grp.Sum(a => a.length_fact), free_length = grp.Sum(a => a.free_length_fact), plan_reis = grp.Sum(a => a.reis), fact_reis = grp.Sum(a => a.reis_fact), work_time = grp.Sum(a => a.WORK_TIME), rest_time = grp.Sum(a => a.rest_time), plan = grp.Sum(a => a.price), cash_fact = grp.Sum(a => a.price_fact) };
            var grpStationTable = from o in StationTable group o by new { o.id_vehicleType, o.id_routetype } into grp select new { id_vehicleType = grp.Key.id_vehicleType, id_routetype = grp.Key.id_routetype, qnt_tickets = grp.Sum(a => a.qnt_tickets), summa = grp.Sum(a => a.summa_tickets), summa_insurance = grp.Sum(a => a.sum_insurance), summa_bagazh = grp.Sum(a => a.bagazh) };
            var svod = from o in grpInfo join b in grpStationTable on new { o.id_routetype, o.id_vehicleType } equals new { b.id_routetype, b.id_vehicleType } into gj from subb in gj.DefaultIfEmpty() select new { id_vehicleType = o.id_vehicleType, id_routetype = o.id_routetype, length = o.length, free_length = o.free_length, fact_reis = o.fact_reis, plan_reis = o.plan_reis, plan = o.plan, cash = (o.cash_fact == null ? 0 : o.cash_fact), work_time = o.work_time, rest_time = o.rest_time, qnt_tickets = (subb.qnt_tickets == null ? 0 : subb.qnt_tickets), summ_tickets = (subb.summa == null ? 0 : subb.summa), summ_insurance = (subb.summa_insurance == null ? 0 : subb.summa_insurance), summ_bagazh = (subb.summa_bagazh == null ? 0 : subb.summa_bagazh) };
            var fullinfo1 = from o in svod join b in db.RouteType on o.id_routetype equals b.id into gj from z in gj.DefaultIfEmpty() select new { id_vehicleType = o.id_vehicleType, routeType = (z.name == null ? "<вид. марш. не опред.>" : z.name.Trim()), length = o.length, free_length = o.free_length, reis_fact = o.fact_reis, reis_plan = o.plan_reis, plan = o.plan, cash = o.cash, workTime = o.work_time, restTime = o.rest_time, people_ticket = o.qnt_tickets, station = o.summ_tickets, insurance = o.summ_insurance, bagazh = o.summ_bagazh };
            var fullinfo = from o in fullinfo1 join b in db.vehicle_type on o.id_vehicleType equals b.id into gj from z in gj.DefaultIfEmpty() select new { VehicleType = (z.name == null ? "<модель. не опред.>" : z.name.Trim()), routeType = o.routeType, length = o.length, free_length = o.free_length, reis_fact = o.reis_fact, reis_plan = o.reis_plan, plan = o.plan, cash = o.cash, workTime = o.workTime, restTime = o.restTime, people_ticket = o.people_ticket, station = o.station, insurance = o.insurance, bagazh = o.bagazh };
            var resultinfo = from o in fullinfo orderby o.VehicleType, o.routeType select new TechReportDataByVehicleModel { vehicleModel = o.VehicleType, routeType = o.routeType, workTime = o.workTime == null ? 0 : o.workTime.Value, restTime = o.restTime == null ? 0 : o.restTime.Value, length = o.length == null ? 0 : o.length.Value, free_length = o.free_length == null ? 0 : o.free_length.Value, cash = o.cash == null ? 0 : o.cash.Value, station = o.station, insurance = o.insurance, bagazh = o.bagazh, plan = o.plan == null ? 0 : o.plan.Value, people_ticket = o.people_ticket, reis_plan = o.reis_plan == null ? 0 : o.reis_plan.Value, reis_fact = o.reis_fact == null ? 0 : o.reis_fact.Value };
            return resultinfo;
        }

        public IEnumerable<TechReportDataVsVehicleType> makeReportTechReportDetailByVehicleModel(DateTime beg_date, DateTime end_date)
        {
            IQueryable<RouteList> RL = from o in db.RouteList where o.dt_datedoc >= beg_date && o.dt_datedoc < end_date select o;
            IQueryable<VokzalTable> StationTable = from o in db.VokzalTable join a in RL on o.id_routelist equals a.id select o;
            IQueryable<RouteListInfo> Info = from o in db.RouteListInfo join a in RL on o.id_rl equals a.id select o;
            var grpInfo = from o in Info group o by new {o.RouteList.vehicle.id_type, o.id_route } into grp select new {id_vehicleType = grp.Key.id_type, id_route = grp.Key.id_route, length = grp.Sum(a => a.length_fact), free_length = grp.Sum(a => a.free_length_fact), plan_reis = grp.Sum(a => a.reis), fact_reis = grp.Sum(a => a.reis_fact), work_time = grp.Sum(a => a.WORK_TIME), rest_time = grp.Sum(a => a.rest_time), plan = grp.Sum(a => a.price), cash_fact = grp.Sum(a => a.price_fact) };
            var grpStationTable = from o in StationTable group o by new {o.RouteList.vehicle.id_type , o.id_route} into grp select new { id_vehicleType=grp.Key.id_type, id_route = grp.Key.id_route, qnt_tickets = grp.Sum(a => a.qnt_tickets), summa = grp.Sum(a => a.summa_tickets), summa_insurance = grp.Sum(a => a.sum_insurance), summa_bagazh = grp.Sum(a => a.bagazh) };
            var svod = from o in grpInfo join b in grpStationTable on new {o.id_vehicleType,fd =  (o.id_route==null)?0:o.id_route.Value } equals new{b.id_vehicleType,fd= b.id_route} into gj from subb in gj.DefaultIfEmpty() select new {id_vehicleType=o.id_vehicleType, id_route = o.id_route, length = o.length, free_length = o.free_length, fact_reis = o.fact_reis, plan_reis = o.plan_reis, plan = o.plan, cash = (o.cash_fact == null ? 0 : o.cash_fact), work_time = o.work_time, rest_time = o.rest_time, qnt_tickets = (subb.qnt_tickets == null ? 0 : subb.qnt_tickets), summ_tickets = (subb.summa == null ? 0 : subb.summa), summ_insurance = (subb.summa_insurance == null ? 0 : subb.summa_insurance), summ_bagazh = (subb.summa_bagazh == null ? 0 : subb.summa_bagazh) };
            var svod1 = from o in svod join b in db.vehicle_type on o.id_vehicleType equals b.id select new {vehicletype = b.name, id_route=o.id_route, length=o.length, free_length=o.free_length, fact_reis=o.fact_reis, plan_reis=o.plan_reis, plan=o.plan, cash=o.cash, work_time=o.work_time, rest_time=o.rest_time, qnt_tickets=o.qnt_tickets, summ_tickets=o.summ_tickets, summ_insurance=o.summ_insurance, summ_bagazh=o.summ_bagazh };
            var fullinfo = from o in svod1 join b in db.Routes on o.id_route equals b.id into gj from z in gj.DefaultIfEmpty() select new {vehicletype = o.vehicletype, routeInfo = (z.number == null ? "<марш. не опред.>" : z.number.Trim() + " " + z.name.Trim()), averPriceRoute = (z.price == null ? 0 : z.price), routeType = (z.RouteType.name == null ? "<вид не опред.>" : z.RouteType.name), length = o.length, free_length = o.free_length, reis_fact = o.fact_reis, reis_plan = o.plan_reis, plan = o.plan, cash = o.cash, workTime = o.work_time, restTime = o.rest_time, people_ticket = o.qnt_tickets, station = o.summ_tickets, insurance = o.summ_insurance, bagazh = o.summ_bagazh };
            var resultinfo = from o in fullinfo orderby o.vehicletype,o.routeType, o.routeInfo select new TechReportDataVsVehicleType {vehicleType=o.vehicletype, routeInfo = o.routeInfo, routeType = o.routeType, averPriceRoute = o.averPriceRoute == null ? 0 : o.averPriceRoute.Value, workTime = o.workTime == null ? 0 : o.workTime.Value, restTime = o.restTime == null ? 0 : o.restTime.Value, length = o.length == null ? 0 : o.length.Value, free_length = o.free_length == null ? 0 : o.free_length.Value, cash = o.cash == null ? 0 : o.cash.Value, station = o.station, insurance = o.insurance, bagazh = o.bagazh, plan = o.plan == null ? 0 : o.plan.Value, people_ticket = o.people_ticket, people_cash = (o.averPriceRoute != null && o.averPriceRoute.Value != 0 && o.cash != null) ? (o.cash.Value / o.averPriceRoute.Value) : 0, reis_plan = o.reis_plan == null ? 0 : o.reis_plan.Value, reis_fact = o.reis_fact == null ? 0 : o.reis_fact.Value };
            return resultinfo;
        }

        public IEnumerable<VehicleLength> makeLengthReport(DateTime beg_date, DateTime end_date)
        {
            IQueryable<RouteList> RL = from o in db.RouteList where o.dt_datedoc >= beg_date && o.dt_datedoc < end_date select o;
            IQueryable<RouteListInfo> Info = from o in db.RouteListInfo join a in RL on o.id_rl equals a.id select o;
            var grpInfo = from o in Info group o by o.RouteList.id_vehicle into grp select new { id_vehicle = grp.Key, length = grp.Sum(a => a.length_fact) };
            var resultInfo = from o in grpInfo join b in db.vehicle on o.id_vehicle equals b.id orderby b.gos_number select new VehicleLength { vehicle = b.gos_number, length = o.length.HasValue ? o.length.Value : 0 };
            return resultInfo;
        }

    }

    public class VehicleLength
    {
        public string vehicle { get; set; }
        public double length{get; set;}
    }
    
    public class TechReportData
    {
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public double averPriceRoute { get; set; }
        public double workTime {get; set;}
        public double restTime { get; set; }
        public double length { get; set; }
        public double free_length { get; set; }
        public double cash { get; set; }
        public double station { get; set; }
        public double insurance { get; set; }
        public double bagazh { get; set; }
        public double plan { get; set; }
        public double people_ticket { get; set; }
        public double people_cash { get; set; }
        public double reis_plan { get; set; }
        public double reis_fact { get; set; }
    }

    public class TechReportDataVsVehicleType
    {
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public string vehicleType { get; set; }
        public double averPriceRoute { get; set; }
        public double workTime { get; set; }
        public double restTime { get; set; }
        public double length { get; set; }
        public double free_length { get; set; }
        public double cash { get; set; }
        public double station { get; set; }
        public double insurance { get; set; }
        public double bagazh { get; set; }
        public double plan { get; set; }
        public double people_ticket { get; set; }
        public double people_cash { get; set; }
        public double reis_plan { get; set; }
        public double reis_fact { get; set; }
    }

    public class TechReportDataByDriver
    {
        public string driverTabel { get; set; }
        public string driverFIO { get; set; }
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public double workTime { get; set; }
        public double restTime { get; set; }
        public double length { get; set; }
        public double free_length { get; set; }
        public double cash { get; set; }
        public double station { get; set; }
        public double insurance { get; set; }
        public double bagazh { get; set; }
        public double plan { get; set; }
        public double people_ticket { get; set; }
        public double reis_plan { get; set; }
        public double reis_fact { get; set; }
    }

    public class TechReportDataByVehicleModel
    {
        public string vehicleModel { get; set; }
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public double workTime { get; set; }
        public double restTime { get; set; }
        public double length { get; set; }
        public double free_length { get; set; }
        public double cash { get; set; }
        public double station { get; set; }
        public double insurance { get; set; }
        public double bagazh { get; set; }
        public double plan { get; set; }
        public double people_ticket { get; set; }
        public double reis_plan { get; set; }
        public double reis_fact { get; set; }
    }

    public class DetailTechReportData
    {
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public double? averPriceRoute { get; set; }
        public string routeListData { get; set; }
        public string workTime { get; set; }
        public string restTime { get; set; }
        public string length { get; set; }
        public string free_length { get; set; }
        public string cash { get; set; }
        public string station { get; set; }
        public string insurance { get; set; }
        public string bagazh { get; set; }
        public string plan { get; set; }
        public string people_ticket { get; set; }
        public string people_cash { get; set; }
        public string reis_plan { get; set; }
        public string reis_fact { get; set; }
    }

    public class DriverReportData
    {
        public string driverData { get; set; }
        public string routeInfo { get; set; }
        public string routeType { get; set; }
        public double? averPriceRoute { get; set; }
        public string workTime { get; set; }
        public string restTime { get; set; }
        public string length { get; set; }
        public string free_length { get; set; }
        public string cash { get; set; }
        public string station { get; set; }
        public string insurance { get; set; }
        public string bagazh { get; set; }
        public string plan { get; set; }
        public string people_ticket { get; set; }
        public string people_cash { get; set; }
        public string reis_plan { get; set; }
        public string reis_fact { get; set; }
    }

    public class DetailDriverReportData
    {
        public string driverData { get; set; }
        public string routeInfo { get; set; }
        public string routeListData { get; set; }
        public string routeType { get; set; }
        public double? averPriceRoute { get; set; }
        public string workTime { get; set; }
        public string restTime { get; set; }
        public string length { get; set; }
        public string free_length { get; set; }
        public string cash { get; set; }
        public string station { get; set; }
        public string insurance { get; set; }
        public string bagazh { get; set; }
        public string plan { get; set; }
        public string people_ticket { get; set; }
        public string people_cash { get; set; }
        public string reis_plan { get; set; }
        public string reis_fact { get; set; }
    }
}
